CREATE FUNCTION asi_GetTransactionInvoiceDetails
    (
      @InvoiceReferenceNumber int,
      @OrderNumber numeric(15,2)
    )
RETURNS @TransInvoice TABLE
    (
      ORDER_NUMBER numeric(15,2) NOT NULL DEFAULT(0),
      TAX_1 numeric(15,2) NOT NULL DEFAULT(0),
      TAX_2 numeric(15,2) NOT NULL DEFAULT(0),
      TAX_3 numeric(15,2) NOT NULL DEFAULT(0),
      QUANTITY_ORDERED numeric(15, 6) NOT NULL DEFAULT ( 0 ) ,
      QUANTITY_BACKORDERED numeric(15, 6) NOT NULL DEFAULT ( 0 ) ,
      OL_DESCRIPTION varchar(255) NOT NULL DEFAULT ( '' ) ,
      BOOTH_NUMBERS varchar(255) NOT NULL DEFAULT ( '' ) ,
      QUANTITY_SHIPPED numeric(15, 6) NOT NULL DEFAULT ( 0 ) ,
      UNIT_PRICE money NOT NULL DEFAULT ( 0 ) ,
      EXTENDED_AMOUNT money NOT NULL DEFAULT ( 0 ) ,
      PRODUCT_CODE varchar(31) NOT NULL DEFAULT ( '' ),
      IS_FR_ITEM bit NOT NULL DEFAULT(0),
      LINE_NUMBER numeric(15,2) NOT NULL DEFAULT(0),
      INVOICE_NUMBER int NOT NULL DEFAULT(0),
      INVOICE_REFERENCE_NUM int NOT NULL DEFAULT(0),
      INVOICE_DESC varchar(100) NOT NULL DEFAULT(''),
      CUSTOMER_REFERENCE varchar(40) NOT NULL DEFAULT(''),
      BT_ID varchar(10) NOT NULL DEFAULT(''),
      INVOICE_DATE datetime NULL,
      LINE_TOTAL money NOT NULL DEFAULT(0),
      TOTAL_CHARGES money NOT NULL DEFAULT(0),
      TOTAL_PAYMENTS money NOT NULL DEFAULT(0),
      BALANCE money NOT NULL DEFAULT(0),
      KIT_ITEM_TYPE tinyint NOT NULL DEFAULT(0),
      OL_TAX1 numeric(15,4) NOT NULL DEFAULT(0),
      ORIGINAL_INVOICE int NOT NULL DEFAULT(0),
      VAT_CODE1 varchar(100) NOT NULL DEFAULT(''),
      VAT_CODE2 varchar(100) NOT NULL DEFAULT(''),
      VAT_CODE3 varchar(100) NOT NULL DEFAULT(''),
      VAT_DESC1 varchar(100) NOT NULL DEFAULT(''),
      VAT_DESC2 varchar(100) NOT NULL DEFAULT(''),
      VAT_DESC3 varchar(100) NOT NULL DEFAULT(''),
      VAT_RATE1 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_RATE2 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_RATE3 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_NET1 money NOT NULL DEFAULT(0),
      VAT_NET2 money NOT NULL DEFAULT(0),
      VAT_NET3 money NOT NULL DEFAULT(0),
      VAT_VAT1 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_VAT2 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_VAT3 numeric(15,4) NOT NULL DEFAULT(0),
      VAT_TOT1 money NOT NULL DEFAULT(0),
      VAT_TOT2 money NOT NULL DEFAULT(0),
      VAT_TOT3 money NOT NULL DEFAULT(0)      
    )
AS    
BEGIN
    -- This is used for multiple invoices for event registrations, and is used to help populate the
    -- Rpt_InvoiceTbl table. It calculates data that is otherwise available in Order_Lines and Orders
    -- when not using this feature.
        
    DECLARE @OriginalInvoiceRefNumber int;
    DECLARE @OriginalInvoiceNumber int;
    DECLARE @InvoiceDesc varchar(100);
    DECLARE @Tax1 numeric(15,2);
    DECLARE @LineTotal money;
    DECLARE @TotalCharges money;
    DECLARE @TotalPayments money;
    DECLARE @Balance money;
    DECLARE @QuantityInPriceAdjRows TABLE
    (
        PRODUCT_CODE varchar(31) NOT NULL DEFAULT ( '' ),
        QUANTITY numeric(15, 6) NOT NULL DEFAULT ( 0 )
    );
    DECLARE @TempTransInvoice TABLE
    (
        TRANS_NUMBER int NOT NULL DEFAULT(0),
        LINE_NUMBER int NOT NULL DEFAULT(0),
        PRODUCT_CODE varchar(31) NOT NULL DEFAULT(''),
        OL_DESCRIPTION varchar(255) NOT NULL DEFAULT(''),
        TAX_1 numeric(15,2) NOT NULL DEFAULT(0),
        QUANTITY numeric(15,4) NOT NULL DEFAULT(0),
        UNIT_PRICE money NOT NULL DEFAULT(0),
        AMOUNT money NOT NULL DEFAULT(0),
        PRICE_ADJ bit NOT NULL DEFAULT(0),
        TAX_AUTHORITY varchar(15) NOT NULL DEFAULT(''),
        TAX_RATE numeric(15,4) NOT NULL DEFAULT(0),
        GL_ACCOUNT varchar(50) NOT NULL DEFAULT('')
    );
    DECLARE @TaxCalc TABLE
    (
          ROW_NUM int NOT NULL DEFAULT(0),
          VAT_CODE varchar(100) NOT NULL DEFAULT(''),
          VAT_DESC varchar(100) NOT NULL DEFAULT(''),
          VAT_RATE numeric(15,4) NOT NULL DEFAULT(0),
          VAT_NET money NOT NULL DEFAULT(0),
          VAT_VAT numeric(15,4) NOT NULL DEFAULT(0),
          VAT_TOT money NOT NULL DEFAULT(0)
    );
    DECLARE @RowNum int;    

    
    -- Get the original invoice number
    SELECT TOP 1 @OriginalInvoiceRefNumber = t.INVOICE_REFERENCE_NUM
        FROM Trans t (NOLOCK)
        INNER JOIN Orders o (NOLOCK) ON t.TRANS_NUMBER=o.ORIGINATING_TRANS_NUM
        WHERE o.ORDER_NUMBER=@OrderNumber;
    
    -- first of all let's get all the relevant transactions
    INSERT  INTO @TempTransInvoice
            (
              TRANS_NUMBER,
              LINE_NUMBER,
              PRODUCT_CODE,
              OL_DESCRIPTION ,
              TAX_1,
              QUANTITY,
              UNIT_PRICE,
              AMOUNT,
              PRICE_ADJ,
              TAX_AUTHORITY,
              TAX_RATE,
              GL_ACCOUNT
            )          
            SELECT  
            t.TRANS_NUMBER,
            t.LINE_NUMBER,
            t.PRODUCT_CODE,
            p.TITLE,
            t.TAX_1,
            t.QUANTITY,
            t.UNIT_PRICE,
            t.AMOUNT,
            t.PRICE_ADJ,
            t.TAX_AUTHORITY,
            t.TAX_RATE,
            t.GL_ACCOUNT
            FROM Trans t (NOLOCK)
            INNER JOIN Product p (NOLOCK) ON t.PRODUCT_CODE=p.PRODUCT_CODE
            WHERE t.INVOICE_REFERENCE_NUM=@InvoiceReferenceNumber
            AND p.PROD_TYPE<>'TAX' AND t.TRANSACTION_TYPE='DIST' AND t.POSTED=2;
          
    -- now load them into the TransInvoice table
    -- NOTE unit price won't be right until we divide by the adjusted quantity - see below
    INSERT  INTO @TransInvoice
            (
              OL_TAX1,
              QUANTITY_ORDERED ,
              OL_DESCRIPTION ,
              UNIT_PRICE ,
              EXTENDED_AMOUNT ,
              PRODUCT_CODE
            )          
            SELECT  
            SUM(TAX_1) AS TAX_1,
            SUM(QUANTITY) AS QUANTITY_ORDERED,
            OL_DESCRIPTION,
            SUM(AMOUNT*-1) AS UNIT_PRICE,
            SUM(AMOUNT*-1) AS EXTENDED_AMOUNT,
            PRODUCT_CODE
            FROM @TempTransInvoice
            GROUP BY OL_DESCRIPTION,PRODUCT_CODE;
                
    -- now remove all the quantities that were in PRICE_ADJ rows
    -- and which also appear in non-PRICE_ADJ rows
    INSERT INTO @QuantityInPriceAdjRows
        (
        PRODUCT_CODE,
        QUANTITY
        )
    SELECT    
        PRODUCT_CODE,
        SUM(QUANTITY)        
        FROM @TempTransInvoice        
        WHERE PRICE_ADJ=1 AND PRODUCT_CODE IN
            (
            SELECT PRODUCT_CODE FROM @TempTransInvoice WHERE PRICE_ADJ=0
            )
        GROUP BY PRODUCT_CODE;

    -- now remove all the quantities where we have double entries of PRICE_ADJ for a function,
    -- and no other non-PRICE_ADJ entries
    INSERT INTO @QuantityInPriceAdjRows
        (
        PRODUCT_CODE,
        QUANTITY
        )
    SELECT    
        PRODUCT_CODE,
        SUM(QUANTITY) -(SUM(QUANTITY)/COUNT(*))
        FROM @TempTransInvoice        
        WHERE PRICE_ADJ=1 AND PRODUCT_CODE NOT IN
            (
            SELECT PRODUCT_CODE FROM @TempTransInvoice WHERE PRICE_ADJ=0
            )
        GROUP BY PRODUCT_CODE
        HAVING COUNT(*)>1;    
                
    -- now remove the quantities from the main table which were in price adjust rows
    UPDATE @TransInvoice
        SET QUANTITY_ORDERED=QUANTITY_ORDERED-QUANTITY
        FROM @QuantityInPriceAdjRows
        WHERE [@TransInvoice].PRODUCT_CODE=[@QuantityInPriceAdjRows].PRODUCT_CODE;
    
    -- now finally we can set the unit price
    UPDATE @TransInvoice
        SET UNIT_PRICE=UNIT_PRICE/QUANTITY_ORDERED
        WHERE QUANTITY_ORDERED<>0;
        
    -- sum up the totals
    SELECT @Tax1 = CONVERT(numeric(15,2),SUM(OL_TAX1)) FROM @TransInvoice;
    SELECT @LineTotal=SUM(EXTENDED_AMOUNT) FROM @TransInvoice;
    SET @TotalCharges=@LineTotal+CONVERT(money,@Tax1);
    
    -- get the total payments
    SELECT @TotalPayments=ISNULL(SUM(AMOUNT),0)
        FROM Trans t (NOLOCK)
        WHERE t.INVOICE_REFERENCE_NUM=@InvoiceReferenceNumber
        AND TRANSACTION_TYPE='PAY';
            
    SET @Balance=@TotalCharges-@TotalPayments;
    
    -- only set invoice description and original invoice number
    -- if the current invoice number is different to the orignal
    -- invoice number
    IF @InvoiceReferenceNumber=@OriginalInvoiceRefNumber
    BEGIN    
        SET @InvoiceDesc = '';
        SET @OriginalInvoiceNumber = 0;
    END
    ELSE
    BEGIN
        IF @TotalCharges>0
            SET @InvoiceDesc='Adjustment';
        ELSE
            SET @InvoiceDesc='Credit Note';
            
        -- get the original invoice printed number
        SELECT @OriginalInvoiceNumber=INVOICE_NUM FROM Invoice
            WHERE REFERENCE_NUM=@OriginalInvoiceRefNumber;
    END;
    
    -- now set the QUANTITY_SHIPPED equal to QUANTITY_ORDERED
    -- and set all rows with the reference number and the original invoice number
    -- and set the totals calculated above
    UPDATE @TransInvoice
        SET QUANTITY_SHIPPED=QUANTITY_ORDERED,
            ORDER_NUMBER=@OrderNumber,
            TAX_1=@Tax1,
            LINE_TOTAL=@LineTotal,
            TOTAL_CHARGES=@TotalCharges,
            TOTAL_PAYMENTS=@TotalPayments,
            BALANCE=@Balance,
            INVOICE_REFERENCE_NUM=@InvoiceReferenceNumber,
            INVOICE_DESC=@InvoiceDesc,
            ORIGINAL_INVOICE=@OriginalInvoiceNumber;
    
    -- the following columns just have the default value already set
    -- TAX_2,TAX_3,IS_FR_ITEM,
    
    -- set some of the values from the Invoice table
    UPDATE @TransInvoice
      SET CUSTOMER_REFERENCE=Invoice.CUSTOMER_REFERENCE,
          INVOICE_DATE=Invoice.INVOICE_DATE,       
          INVOICE_NUMBER=Invoice.INVOICE_NUM,
          BT_ID=Invoice.BT_ID
          FROM Invoice (NOLOCK)
          WHERE Invoice.REFERENCE_NUM=[@TransInvoice].INVOICE_REFERENCE_NUM;

    -- now work out the VAT columns
    INSERT INTO @TaxCalc (VAT_CODE,VAT_RATE)
    SELECT DISTINCT TOP 3 TAX_AUTHORITY,TAX_RATE
        FROM @TempTransInvoice
        WHERE TAX_AUTHORITY<>'';

    -- set rows to 1,2 and 3
    SET @RowNum = 0;
    UPDATE @TaxCalc
        SET ROW_NUM=@RowNum,
        @RowNum=@RowNum+1;
        
    -- now set the title        
    UPDATE @TaxCalc
        SET VAT_DESC=TITLE_KEY    
        FROM Product (NOLOCK)
        WHERE PRODUCT_MAJOR='TAX' AND PRODUCT_MINOR=VAT_CODE;

    -- now set the other calculated values
    UPDATE @TaxCalc
        SET VAT_NET=SUM_EXTENDED_AMOUNT,
        VAT_VAT=SUM_TAX_1
        FROM
        (SELECT TAX_AUTHORITY, SUM(AMOUNT*-1) AS SUM_EXTENDED_AMOUNT, SUM(TAX_1) AS SUM_TAX_1
        FROM @TempTransInvoice
        GROUP BY TAX_AUTHORITY)
        AS EXTENDED_AMOUNTS
        WHERE VAT_CODE=EXTENDED_AMOUNTS.TAX_AUTHORITY;

    UPDATE @TaxCalc
        SET VAT_TOT=VAT_NET+VAT_VAT;    

    -- now set the values in @TransInvoice
    UPDATE @TransInvoice
        SET VAT_CODE1=VAT_CODE,
            VAT_DESC1=VAT_DESC,
            VAT_RATE1=VAT_RATE,
            VAT_NET1=VAT_NET,
            VAT_VAT1=VAT_VAT,
            VAT_TOT1=VAT_TOT
        FROM @TaxCalc WHERE ROW_NUM=1;

    UPDATE @TransInvoice
        SET VAT_CODE2=VAT_CODE,
            VAT_DESC2=VAT_DESC,
            VAT_RATE2=VAT_RATE,
            VAT_NET2=VAT_NET,
            VAT_VAT2=VAT_VAT,            
            VAT_TOT2=VAT_TOT
        FROM @TaxCalc WHERE ROW_NUM=2;
        
    UPDATE @TransInvoice
        SET VAT_CODE3=VAT_CODE,
            VAT_DESC3=VAT_DESC,
            VAT_RATE3=VAT_RATE,
            VAT_NET3=VAT_NET,
            VAT_VAT3=VAT_VAT,            
            VAT_TOT3=VAT_TOT
        FROM @TaxCalc WHERE ROW_NUM=3;        
                              
    RETURN;

END ;

GO